﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace DAL.Service.Impl
{
    public class UserCardServiceImpl:BaseServiceImpl,IUserCardService
    {

        public int delete(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("DELETE FROM joy.UserCard WHERE uc_id =@id");
            query.addParas("@id", id);
            return base.exeUpdateSql(query);
        }

        public IList getUserCardbyid(int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT * FROM joy.UserCard WHERE uc_id=@id");
            query.addParas("@id", id);
            return base.getList(query);
        }

        public IList getUserCard(string card, string in_s, string in_e, int pageno, int pagesize)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = " where 1=1";
            if (!String.IsNullOrEmpty(card))
            {
                _sql += " and uc_card like '%"+card+"%'";
            }

            if (!String.IsNullOrEmpty(in_s))
            {
                _sql += " and uc_addTime >= @in_s";
                query.addParas("@in_s", in_s);
            }
            if (!String.IsNullOrEmpty(in_e))
            {
                in_e = in_e + " 23:59:59";
                _sql += " and uc_addTime < @in_e";
                query.addParas("@in_e", in_e);
            }

            if (pageno == 1)
            {
                sb.Append("SELECT count(*) as total FROM joy.UserCard " + _sql + ";");
                sb.Append("select top " + pagesize + " *,case uc_state when 0 then '未用' when 1 then '已用' else '未知'  end uc_statetext from joy.UserCard " + _sql + "  order by uc_id desc;");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select *,case uc_state when 0 then '未用' when 1 then '已用' else '未知'  end uc_statetext,ROW_NUMBER() OVER (order by uc_id desc) as rno from  joy.UserCard " + _sql);
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by uc_id desc;");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }

        public int getCountByCard(string card, int id)
        {
            MyQuery query = new MyQuery();
            query.setQueryString(" select count(1) as total from joy.UserCard where uc_card='" + card + "'" + " and uc_id !=" + id);
            Hashtable ht = (Hashtable)this.getList(query)[0];
            return Convert.ToInt32(ht["total"]);
        }
        public IList getCard(string card, int state)
        {
            MyQuery query = new MyQuery();
            query.setQueryString(" select * from joy.UserCard where uc_card=@card and uc_state=@state ");
            query.addParas("@card", card);
            query.addParas("@state", state);
            return this.getList(query);
        }

        public int changestate(int ucid, string cmode, string ckf, string cyj, string cbz, string adminid)
        {
            MyQuery query = new MyQuery();
            //query.setQueryString("update joy.UserCard set uc_state=1,uc_chargeMode=@cmode,uc_kf=@ckf,uc_yj=@cyj,uc_bz=@cbz,uc_lastUser=@adminid,uc_updateTime=getdate() WHERE uc_id =@ucid");
            query.setQueryString("update joy.UserCard set uc_state=1,uc_kf=@ckf,uc_yj=@cyj,uc_bz=@cbz,uc_lastUser=@adminid,uc_updateTime=getdate() WHERE uc_id =@ucid");
            query.addParas("@ucid", ucid);
            //query.addParas("@cmode", cmode);
            query.addParas("@ckf", ckf);
            query.addParas("@cyj", cyj);
            query.addParas("@cbz", cbz);
            query.addParas("@adminid", adminid);
            return base.exeUpdateSql(query);
        }

        public int updateState(int id, int state, int lastUser)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("update joy.UserCard set uc_state=@state,uc_lastUser=@userid,uc_updateTime=getdate() where uc_id=@id ");
            query.addParas("@state", state);
            query.addParas("@userid", lastUser);
            query.addParas("@id", id);
            return this.exeUpdateSql(query);
        }

        public IList getUserCard(string card,int type,int state, string in_s, string in_e,
            string sx_s, string sx_e,
            string dq_s, string dq_e,
            int ordercard,int ordertype,int orderstate,int orderdate,int orderuser,
            int pageno, int pagesize)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = " where 1=1";
            if (!String.IsNullOrEmpty(card))
            {
                _sql += " and uc_card like '%" + card + "%'";
            }
            if (type>-1)
            {
                _sql += " and uc_chargeMode= "+type;
            }
            if (state > -1)
            {
                _sql += " and uc_state =" + state;
            }
            if (!String.IsNullOrEmpty(in_s))
            {
                _sql += " and uc_addTime >= @in_s";
                query.addParas("@in_s", in_s);
            }
            if (!String.IsNullOrEmpty(in_e))
            {
                in_e = in_e + " 23:59:59";
                _sql += " and uc_addTime < @in_e";
                query.addParas("@in_e", in_e);
            }
            if (!String.IsNullOrEmpty(sx_s))
            {
                _sql += " and c_startTime >= @sx_s";
                query.addParas("@sx_s", sx_s);
            }
            if (!String.IsNullOrEmpty(sx_e))
            {
                sx_e = sx_e + " 23:59:59";
                _sql += " and c_startTime < @sx_e";
                query.addParas("@sx_e", sx_e);
            }
            if (!String.IsNullOrEmpty(dq_s))
            {
                _sql += " and c_endTime >= @dq_s";
                query.addParas("@dq_s", dq_s);
            }
            if (!String.IsNullOrEmpty(dq_e))
            {
                dq_e = dq_e + " 23:59:59";
                _sql += " and c_endTime < @dq_e";
                query.addParas("@dq_e", dq_e);
            }
            string _order = "";
            if (ordercard > -1)
                _order += _order == "" ? " order by uc_card desc" : " ,uc_card desc";
            if (ordertype > -1)
                _order += _order == "" ? " order by uc_chargeMode desc" : " ,uc_chargeMode desc";
            if (orderstate > -1)
                _order += _order == "" ? " order by uc_state desc" : " ,uc_state desc";
            if (orderdate > -1)
                _order += _order == "" ? " order by c_startTime desc" : " ,c_startTime desc";
            if (orderuser > -1)
                _order += _order == "" ? " order by uc_user desc" : " ,uc_user desc";
            _order += _order == "" ? " order by uc_id desc" : " ,uc_id desc";
            if (pageno == 1)
            {
                sb.Append("select  count(*) as total from joy.UserCard as a left join joy.UserCardCost as b on a.uc_id=b.c_ucid inner join cms.Operator as c on a.uc_user=c.O_id " + _sql + ";");
                sb.Append("select top " + pagesize + " * from joy.UserCard as a left join joy.UserCardCost as b on a.uc_id=b.c_ucid inner join cms.Operator as c on a.uc_user=c.O_id " + _sql + _order + ";");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select *,ROW_NUMBER() OVER (" + _order + ") as rno from joy.UserCard as a left join joy.UserCardCost as b on a.uc_id=b.c_ucid inner join cms.Operator as c on a.uc_user=c.O_id " + _sql);
                sb.Append(") as a where a.rno>@start and a.rno<@end " + _order + ";");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }
    }
}
